package com.elite.groovy.util;

import com.elite.groovy.kh.KHUtil;
import com.wisdge.web.springframework.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.kafka.clients.consumer.ConsumerConfig;
import org.apache.kafka.clients.producer.ProducerConfig;
import org.apache.kafka.common.serialization.Deserializer;
import org.apache.kafka.common.serialization.StringSerializer;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.kafka.core.DefaultKafkaProducerFactory;
import org.springframework.kafka.core.KafkaTemplate;
import org.springframework.kafka.core.ProducerFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class EliteBean {
    private DataSource dataSource;
    private DataSourceTransactionManager transactionManager;

    private static final String STRING_EXAMINE = "type";
    private static final String STRING_VALUE = "value";

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public DataSourceTransactionManager getTransactionManager() {
        return transactionManager;
    }

    public void setTransactionManager(DataSourceTransactionManager transactionManager) {
        this.transactionManager = transactionManager;
    }

    public String getdatasourcename(String dataSourceID) throws SQLException {
        String datasourcename = "";
        Connection connection = null;
        try {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
            connection = jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData md = connection.getMetaData();
            datasourcename = md.getDatabaseProductName();
        } catch (Exception e) {
            log.error("", e);
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return datasourcename;
    }

    //获取不同数据库的时间
    public String getDbCurrentDate(String dataSourceId) throws SQLException {
        String dbType = getdatasourcename(dataSourceId);
        log.debug("dbType:[{}]", dbType);
        if ("ORACLE".equalsIgnoreCase(dbType)) {
            return "sysdate";
        } else if ("SQLSERVER".equalsIgnoreCase(dbType) || "Microsoft sql server".equalsIgnoreCase(dbType)) {
            return "GetDate()";
        } else {
            return "now()";
        }
    }

    //获取不同数据库的uuid
    public String getDbUuid(String dataSourceId) throws SQLException {
        String dbType = getdatasourcename(dataSourceId);
        log.debug("dbType:[{}]", dbType);
        if ("ORACLE".equalsIgnoreCase(dbType)) {
            return "SYS_GUID()";
        } else if ("SQLSERVER".equalsIgnoreCase(dbType) || "Microsoft sql server".equalsIgnoreCase(dbType) || "DM DBMS".equalsIgnoreCase(dbType)) {
            return "NEWID()";
        } else if ("PostgreSQL".equalsIgnoreCase(dbType)) {
            return "gen_random_uuid()";
        } else {
            return "UUID()";
        }
    }

    public int update_sql_pro(String sql, MapSqlParameterSource params, String dataSourceID) {
        int result = 0;
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(getDataSource(dataSourceID));
        result = jdbcTemplate.update(sql, params);
        return result;
    }

    public List chaxun_sql_pro(String sql, MapSqlParameterSource params, String dataSourceID) {
        List result = null;
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(getDataSource(dataSourceID));
        result = jdbcTemplate.queryForList(sql, params);
        return result;
    }

    public SqlRowSet chaxun_sqlRowSet(String sql, String dataSourceID) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        SqlRowSet result = jdbcTemplate.queryForRowSet(sql);
        return result;
    }

    public List chaxun_sql(String sql, String dataSourceID) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        List result = jdbcTemplate.queryForList(sql);
        return result;
    }

    public Map<String, Object> getGroovyScriptByDB(String groovyName) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select GROOVY_NAME, GROOVY_CONTENT, MODIFIED_TIME from TAB_GROOVY_SCRIPT where GROOVY_NAME = ?";
        return jdbcTemplate.query(sql, rs -> {
            if (rs.next()) {
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("GROOVY_NAME", rs.getString("GROOVY_NAME"));
                resultMap.put("GROOVY_CONTENT", rs.getString("GROOVY_CONTENT"));
                resultMap.put("MODIFIED_TIME", rs.getTimestamp("MODIFIED_TIME"));
                return resultMap;
            }
            return null;
        }, groovyName);
    }

    public List<Map<String, Object>> listAllGroovyScripts() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select GROOVY_NAME, GROOVY_CONTENT, MODIFIED_TIME from TAB_GROOVY_SCRIPT";
        return jdbcTemplate.query(sql, rs -> {
            List<Map<String, Object>> gss = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("GROOVY_NAME", rs.getString("GROOVY_NAME"));
                resultMap.put("GROOVY_CONTENT", rs.getString("GROOVY_CONTENT"));
                resultMap.put("MODIFIED_TIME", rs.getTimestamp("MODIFIED_TIME"));
                gss.add(resultMap);
            }
            return gss;
        });
    }

    public int recordHttpLog(Object[] params) throws SQLException {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String nowDate = getDbCurrentDate("dataSource");
        String insertSql = "INSERT INTO sysc_xhr_operate_log (operate_id, trace_id, dyn_id, request_method, operate_url, " + "operate_ip, oper_param, json_result, status_code, error_msg, createdby, createddate, modifiedby, " + "modifieddate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " + nowDate + ", ?, " + nowDate + ")";
        return jdbcTemplate.update(insertSql, params);
    }

    public void query(String sql, String dataSourceId, ResultSetExtractor resultSetExtractor) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceId));
        jdbcTemplate.query(sql, resultSetExtractor);
    }

    public void execute(String sql, String dataSourceID) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        jdbcTemplate.execute(sql);
    }

    public String executeWithReturn(String sql, final String[] temp, String dataSourceID) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        String result = (String) jdbcTemplate.execute(sql, new CallableStatementCallback() {
            @Override
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                for (int i = 0; i < temp.length; i++) {
                    cs.setString(i + 1, temp[i]);//输入参数
                }
                cs.registerOutParameter(temp.length + 1, Types.VARCHAR);//输出参数
                cs.execute();
                return cs.getString(temp.length + 1);
            }
        });
        log.debug("result:" + result);
        return result;
    }

    public List executeWithMultiReturn(String sql, String dataSourceID, final String ParamJsonArray) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));

        String dbType = null;
        try {
            dbType = getdatasourcename(dataSourceID);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        log.debug("Groovy EliteBean executeWithMultiReturn dbType:[{}]", dbType);

        if (!KHUtil.isNull(dbType) && "PostgreSQL".equalsIgnoreCase(dbType)) {

            // 解析参数
            List<String> paramListCus = new ArrayList<>();
            JSONArray jsonIn = JSONArray.fromObject(ParamJsonArray);
            for (int i = 0; i < jsonIn.size(); i++) {
                JSONObject job = jsonIn.getJSONObject(i);
                paramListCus.add(KHUtil.GetSFJV(job, STRING_VALUE));

            }
            log.debug("解析参数 结果:{}", paramListCus);

            // 执行调用
            List<Map<String, Object>> execResList = jdbcTemplate.queryForList(sql, paramListCus);
            log.debug("执行调用 结果:{}", execResList);

            return execResList;

        } else {

            List resultList = (List) jdbcTemplate.execute(sql, new CallableStatementCallback() {
                @Override
                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    JSONArray jsonIn = JSONArray.fromObject(ParamJsonArray);
                    List list = new ArrayList();
                    List returnList = new ArrayList();
                    if (jsonIn != null && jsonIn.size() > 0) {
                        for (int i = 0; i < jsonIn.size(); i++) {
                            JSONObject job = jsonIn.getJSONObject(i);
                            if ("string".equalsIgnoreCase(job.getString(STRING_EXAMINE))) {
                                if ("in".equalsIgnoreCase(job.getString("mode"))) {
                                    cs.setString(i + 1, job.getString(STRING_VALUE));
                                } else {
                                    cs.registerOutParameter(i + 1, Types.VARCHAR);
                                    list.add(i + 1);
                                }
                            } else if ("int".equalsIgnoreCase(job.getString(STRING_EXAMINE))) {
                                if ("in".equalsIgnoreCase(job.getString("mode"))) {
                                    cs.setInt(i + 1, job.getInt(STRING_VALUE));
                                } else {
                                    cs.registerOutParameter(i + 1, Types.INTEGER);
                                    list.add(i + 1);
                                }
                            } else if ("date".equalsIgnoreCase(job.getString(STRING_EXAMINE))) {
                                if ("in".equalsIgnoreCase(job.getString("mode"))) {
                                    cs.setDate(i + 1, new Date(Util.FormatStrDate(job.getString(STRING_VALUE), "yyyy-MM-dd HH:mm:ss").getTime()));
                                } else {
                                    cs.registerOutParameter(i + 1, Types.DATE);
                                    list.add(i + 1);
                                }
                            }
                        }
                    }
                    cs.execute();
                    if (list != null && list.size() > 0) {
                        for (int i = 0; i < list.size(); i++) {
                            int tempNum = Integer.parseInt(String.valueOf(list.get(i)));
                            JSONObject job = jsonIn.getJSONObject(tempNum - 1);
                            if ("string".equalsIgnoreCase(job.getString(STRING_EXAMINE))) {
                                returnList.add(cs.getString(tempNum));
                            } else if ("int".equalsIgnoreCase(job.getString(STRING_EXAMINE))) {
                                returnList.add(cs.getInt(tempNum));
                            } else if ("date".equalsIgnoreCase(job.getString(STRING_EXAMINE))) {
                                returnList.add(cs.getDate(tempNum));
                            }
                        }
                    }
                    return returnList;
                }
            });
            log.debug("result:" + resultList);
            return resultList;
        }
    }

    public int update(String sql, String dataSourceID) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        int resultList = jdbcTemplate.update(sql);
        return resultList;
    }

    public int[] updateBatch(String[] sql, String dataSourceID) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        int[] resultList = jdbcTemplate.batchUpdate(sql);
        return resultList;
    }

    public int[] updateBatch_pro(String sql, final List dataList, String dataSourceID) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource(dataSourceID));
        int[] resultList = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return dataList.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) {
                try {
                    List tempList = (List) dataList.get(i);
                    for (int num = 0; num < tempList.size(); num++) {
                        Map map = (Map) tempList.get(num);
                        String type = (String) map.get(STRING_EXAMINE);
                        if ("string".equalsIgnoreCase(type)) {
                            ps.setString(num + 1, (String) map.get(STRING_VALUE));
                        } else if ("long".equalsIgnoreCase(type)) {
                            ps.setLong(num + 1, (long) map.get(STRING_VALUE));
                        } else if ("int".equalsIgnoreCase(type)) {
                            ps.setInt(num + 1, (int) map.get(STRING_VALUE));
                        } else if ("date".equalsIgnoreCase(type)) {
                            ps.setDate(num + 1, (Date) map.get(STRING_VALUE));
                        } else if ("float".equalsIgnoreCase(type)) {
                            ps.setFloat(num + 1, (float) map.get(STRING_VALUE));
                        }
                    }
                } catch (Exception e) {
                    log.error("", e);
                }
            }
        });
        return resultList;
    }

    public DataSource getDataSource(String dataSourceID) {
        if (dataSourceID != null && dataSourceID.length() > 0) {
            DataSource dataSourceById = SpringContextUtil.getBean(dataSourceID);
            return dataSourceById == null ? dataSource : dataSourceById;
        } else {
            return dataSource;
        }
    }

    public JdbcTemplate getJdbcTemplate(String dataSourceID) {
        return new JdbcTemplate(getDataSource(dataSourceID));
    }

    public Map<String, Object> producerConfigs() {
        Map<String, Object> props = new HashMap<>();
        CommonConfiguration commonConfiguration = SpringContextUtil.getBean("commonConfiguration");
        props.put(ProducerConfig.BOOTSTRAP_SERVERS_CONFIG, commonConfiguration.getKafkaServers());
        props.put(ProducerConfig.KEY_SERIALIZER_CLASS_CONFIG, StringSerializer.class);
        props.put(ProducerConfig.VALUE_SERIALIZER_CLASS_CONFIG, StringSerializer.class);
        return props;
    }

    public ProducerFactory<String, String> producerFactory() {
        return new DefaultKafkaProducerFactory<>(producerConfigs());
    }

    public KafkaTemplate getKafkaTemplate() {
        return new KafkaTemplate(producerFactory());
    }

    public static Map<String, Object> consumerConfigs(Class<? extends Deserializer> keyDeserializeClazz, Class<? extends Deserializer> valueDeserializeClazz) {
        Map<String, Object> props = new HashMap<>();
        CommonConfiguration commonConfiguration = SpringContextUtil.getBean("commonConfiguration");
        props.put(ConsumerConfig.BOOTSTRAP_SERVERS_CONFIG, commonConfiguration.getKafkaServers());
        props.put(ConsumerConfig.GROUP_ID_CONFIG, commonConfiguration.getKafkaGroupId());
        props.put(ConsumerConfig.KEY_DESERIALIZER_CLASS_CONFIG, keyDeserializeClazz);
        props.put(ConsumerConfig.VALUE_DESERIALIZER_CLASS_CONFIG, valueDeserializeClazz);
        props.put(ConsumerConfig.AUTO_OFFSET_RESET_CONFIG, commonConfiguration.getAutoOffsetReset());
        props.put(ConsumerConfig.ENABLE_AUTO_COMMIT_CONFIG, true);
        return props;
    }

}
